﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SQLite;

namespace WpfApp1
{
    class SQLiteHelper
    {
        public SQLiteHelper()
        {

        }

        public void create(string dbname)
        {
            SQLiteConnection.CreateFile(dbname);

        }
        
        public static string ConnectionString = "Data Source=PrinterDB.db;Version=3;";
        

        public DataTable getData(string sql)
        {
           

            SQLiteConnection connection = new SQLiteConnection(ConnectionString);


            SQLiteCommand command = new SQLiteCommand(sql, connection);

            SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
            DataTable data = new DataTable();
            try { adapter.Fill(data); }
            catch (Exception) { throw; }
            return data;

        }

        public DataRow getDataRow(string sql)
        {
            DataRow row = null;
            DataTable dt = getData(sql);
            if (dt.Rows.Count > 0)
            {
                row = dt.Rows[0];
            }
            return row;

        }

        public string getValue(string sql,string field)
        {
            string rs = "";
            DataTable dt=getData(sql);
            if (dt.Rows.Count > 0)
            {
                rs=dt.Rows[0][field].ToString();
            }
            return rs;


        }

        





        public void execute(string sql)
        {
           

            SQLiteConnection connection = new SQLiteConnection(ConnectionString);


            SQLiteCommand command = new SQLiteCommand(sql, connection);

            try
            {
                connection.Open();
            }
            catch
            {

            }
            try
            {
                command.ExecuteNonQuery();
            }
            catch
            {

            }

            connection.Close();


        }

        public int insert(string sql)
        {


            SQLiteConnection connection = new SQLiteConnection(ConnectionString);


            SQLiteCommand command = connection.CreateCommand();

            command.CommandText = sql;
                //new SQLiteCommand(sql, connection);
            int id = 0;

            connection.Open();
            try
            {
                command.ExecuteNonQuery();
                command = new SQLiteCommand("select last_insert_rowid() as 'id'", connection);

                id = Convert.ToInt32(command.ExecuteScalar());

            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception:"+ex.Message);
            }

            
            connection.Close();

            return id;

        }


        public void update(string sql)
        {


            SQLiteConnection connection = new SQLiteConnection(ConnectionString);


            SQLiteCommand command = new SQLiteCommand(sql, connection);

            connection.Open();

            try
            {
                command.ExecuteNonQuery();
            }
            catch
            {

            }
            connection.Close();

           

        }





    }
}
